const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取成绩列表 - 使用 auth 替代 authenticateToken
router.get('/', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }

    const { classId, courseId, status, term } = req.query;
    
    // 构建查询条件
    let conditions = [];
    let params = [];
    
    if (classId && classId !== '') {
      conditions.push('c.id = ?');
      params.push(classId);
    }
    
    if (courseId && courseId !== '') {
      conditions.push('co.id = ?');
      params.push(courseId);
    }
    
    if (status && status !== '' && status !== '全部') {
      if (status === '通过') {
        conditions.push('g.score >= 60');
      } else if (status === '不通过') {
        conditions.push('g.score < 60');
      }
    }
    
    // 移除 term 条件，因为 grades 表中没有 term 字段
    // 可能需要通过课程的学期来筛选
    if (term && term !== '') {
      conditions.push('co.semester = ?');
      params.push(term);
    }
    
    const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
    
    // 修改查询语句，移除不存在的字段
    const [records] = await pool.query(
      `SELECT g.id, u.userId as studentId, u.name as studentName, 
       c.className, co.name as courseName, g.score, co.semester as term
       FROM grades g
       JOIN users u ON g.studentId = u.id
       JOIN classes c ON u.classId = c.id
       JOIN courses co ON g.courseId = co.id
       ${whereClause}
       ORDER BY c.className, u.userId, co.name`,
      params
    );
    
    // 计算统计数据
    let statistics = {
      total: records.length,
      avgScore: 0,
      passRate: 0,
      excellentRate: 0
    };
    
    if (records.length > 0) {
      // 使用 score 字段替代 total
      const totalScore = records.reduce((sum, grade) => sum + grade.score, 0);
      statistics.avgScore = parseFloat((totalScore / records.length).toFixed(1));
      
      const passCount = records.filter(grade => grade.score >= 60).length;
      statistics.passRate = Math.round((passCount / records.length) * 100);
      
      const excellentCount = records.filter(grade => grade.score >= 85).length;
      statistics.excellentRate = Math.round((excellentCount / records.length) * 100);
    }
    
    res.json({
      records,
      statistics
    });
    
  } catch (error) {
    console.error('获取成绩列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取单个成绩详情
router.get('/:id', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { id } = req.params;
    
    const [grades] = await pool.query(
      `SELECT g.*, u.name as studentName, u.userId as studentNumber, 
       c.name as courseName, cl.className
       FROM grades g
       JOIN users u ON g.studentId = u.id
       JOIN courses c ON g.courseId = c.id
       JOIN classes cl ON u.classId = cl.id
       WHERE g.id = ?`,
      [id]
    );
    
    if (grades.length === 0) {
      return res.status(404).json({ message: '成绩记录不存在' });
    }
    
    res.json(grades[0]);
    
  } catch (error) {
    console.error('获取成绩详情错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 添加成绩
router.post('/', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { studentId, courseId, term, score } = req.body; // 只接收必要字段，移除 notes
    
    // 验证必要参数
    if (!studentId || !courseId || !term) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 检查学生是否存在
    const [students] = await pool.query('SELECT id FROM users WHERE id = ? AND role = "student"', [studentId]);
    if (students.length === 0) {
      return res.status(404).json({ message: '学生不存在' });
    }
    
    // 检查课程是否存在
    const [courses] = await pool.query('SELECT id FROM courses WHERE id = ?', [courseId]);
    if (courses.length === 0) {
      return res.status(404).json({ message: '课程不存在' });
    }
    
    // 检查是否已存在该学生该课程的成绩
    const [existingGrades] = await pool.query(
      'SELECT id FROM grades WHERE studentId = ? AND courseId = ? AND term = ?',
      [studentId, courseId, term]
    );
    
    if (existingGrades.length > 0) {
      return res.status(400).json({ message: '该学生该课程的成绩已存在' });
    }
    
    // 插入成绩记录 - 只插入必要字段，移除 notes
    const [result] = await pool.query(
      `INSERT INTO grades (studentId, courseId, term, score, createdAt, updatedAt)
       VALUES (?, ?, ?, ?, NOW(), NOW())`,
      [studentId, courseId, term, score]
    );
    
    res.status(201).json({
      id: result.insertId,
      message: '成绩添加成功'
    });
    
  } catch (error) {
    console.error('添加成绩错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新成绩
router.put('/:id', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { id } = req.params;
    const { score } = req.body; // 只接收 score 字段
    
    // 检查成绩记录是否存在
    const [grades] = await pool.query('SELECT id FROM grades WHERE id = ?', [id]);
    if (grades.length === 0) {
      return res.status(404).json({ message: '成绩记录不存在' });
    }
    
    console.log('更新成绩数据:', { score, id });
    
    // 更新成绩记录 - 只更新 score 字段
    await pool.query(
      `UPDATE grades 
       SET score = ?, updatedAt = NOW()
       WHERE id = ?`,
      [score, id]
    );
    
    res.json({ message: '成绩更新成功' });
    
  } catch (error) {
    console.error('更新成绩错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除成绩
router.delete('/:id', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { id } = req.params;
    
    // 检查成绩记录是否存在
    const [grades] = await pool.query('SELECT id FROM grades WHERE id = ?', [id]);
    if (grades.length === 0) {
      return res.status(404).json({ message: '成绩记录不存在' });
    }
    
    // 删除成绩记录
    await pool.query('DELETE FROM grades WHERE id = ?', [id]);
    
    res.json({ message: '成绩删除成功' });
    
  } catch (error) {
    console.error('删除成绩错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 批量导入成绩
router.post('/import', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { grades } = req.body;
    
    if (!Array.isArray(grades) || grades.length === 0) {
      return res.status(400).json({ message: '无效的成绩数据' });
    }
    
    // 开始事务
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
      let successCount = 0;
      let errorCount = 0;
      let errors = [];
      
      for (const grade of grades) {
        const { studentId, courseId, term, regular, midterm, final, total, level, gpa, notes } = grade;
        
        // 验证必要参数
        if (!studentId || !courseId || !term) {
          errorCount++;
          errors.push(`学生ID:${studentId}, 课程ID:${courseId} - 缺少必要参数`);
          continue;
        }
        
        // 检查是否已存在该学生该课程的成绩
        const [existingGrades] = await connection.query(
          'SELECT id FROM grades WHERE studentId = ? AND courseId = ? AND term = ?',
          [studentId, courseId, term]
        );
        
        if (existingGrades.length > 0) {
          // 更新现有成绩
          await connection.query(
            `UPDATE grades 
             SET regular = ?, midterm = ?, final = ?, total = ?, 
             level = ?, gpa = ?, notes = ?, updatedAt = NOW()
             WHERE studentId = ? AND courseId = ? AND term = ?`,
            [regular, midterm, final, total, level, gpa, notes, studentId, courseId, term]
          );
        } else {
          // 插入新成绩
          await connection.query(
            `INSERT INTO grades (studentId, courseId, term, regular, midterm, final, total, level, gpa, notes, createdAt, updatedAt)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
            [studentId, courseId, term, regular, midterm, final, total, level, gpa, notes]
          );
        }
        
        successCount++;
      }
      
      // 提交事务
      await connection.commit();
      
      res.json({
        message: '成绩导入完成',
        successCount,
        errorCount,
        errors
      });
      
    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
    
  } catch (error) {
    console.error('批量导入成绩错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取班级列表
router.get('/classes', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const [classes] = await pool.query(
      'SELECT id, className as name FROM classes ORDER BY className'
    );
    
    res.json(classes);
    
  } catch (error) {
    console.error('获取班级列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取课程列表
router.get('/courses', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { term } = req.query;
    
    let query = 'SELECT id, name FROM courses';
    let params = [];
    
    if (term) {
      query += ' WHERE semester = ?';
      params.push(term);
    }
    
    query += ' ORDER BY name';
    
    const [courses] = await pool.query(query, params);
    
    res.json(courses);
    
  } catch (error) {
    console.error('获取课程列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取班级学生列表
router.get('/classes/:classId/students', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId } = req.params;
    
    const [students] = await pool.query(
      `SELECT u.id, u.name, u.studentId as studentNumber
       FROM users u
       WHERE u.classId = ? AND u.role = 'student'
       ORDER BY u.studentId`,
      [classId]
    );
    
    res.json(students);
    
  } catch (error) {
    console.error('获取班级学生列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取学期列表
router.get('/terms', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 从课程表中获取所有学期
    const [terms] = await pool.query(
      'SELECT DISTINCT semester as term FROM courses ORDER BY semester DESC'
    );
    
    res.json(terms.map(item => item.term));
    
  } catch (error) {
    console.error('获取学期列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});


// 修改路由定义，使用更明确的路径
router.get('/template', async (req, res) => {  // 暂时移除 auth 中间件进行测试
  console.log('成绩导入模板路由被调用');
  try {
    // 暂时跳过权限验证
    // if (req.user.role !== 'admin' && req.user.role !== 'teacher') {
    //   return res.status(403).json({ message: '权限不足' });
    // }
    
    console.log('开始创建Excel文件...');
    // 使用exceljs创建Excel文件
    const Excel = require('exceljs');
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('成绩导入模板');
    
    // 添加表头
    worksheet.columns = [
      { header: '学号', key: 'studentId', width: 15 },
      { header: '姓名', key: 'studentName', width: 15 },
      { header: '课程编号', key: 'courseId', width: 15 },
      { header: '课程名称', key: 'courseName', width: 20 },
      { header: '学期', key: 'term', width: 15 },
      { header: '成绩', key: 'score', width: 10 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加示例数据
    worksheet.addRow({
      studentId: '2023001',
      studentName: '张三',
      courseId: 'CS101',
      courseName: '计算机基础',
      term: '2023-2024-1',
      score: 85
    });
    
    worksheet.addRow({
      studentId: '2023002',
      studentName: '李四',
      courseId: 'CS101',
      courseName: '计算机基础',
      term: '2023-2024-1',
      score: 92
    });
    
    // 添加说明
    worksheet.addRow({});
    worksheet.addRow({studentId: '说明：请按照以上格式填写成绩数据，然后上传'});
    
    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=grades_template.xlsx');
    
    // 将Excel文件写入响应
    await workbook.xlsx.write(res);
    res.end();
    
  } catch (error) {
    console.error('获取成绩导入模板错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取成绩导入模板 - 确保此路由在 /:id 路由之前定义
router.get('/template', auth, async (req, res) => {
  console.log('成绩导入模板路由被调用');
  try {
    // 暂时跳过权限验证
    // if (req.user.role !== 'admin' && req.user.role !== 'teacher') {
    //   return res.status(403).json({ message: '权限不足' });
    // }
    
    console.log('开始创建Excel文件...');
    // 使用exceljs创建Excel文件
    const Excel = require('exceljs');
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('成绩导入模板');
    
    // 添加表头
    worksheet.columns = [
      { header: '学号', key: 'studentId', width: 15 },
      { header: '姓名', key: 'studentName', width: 15 },
      { header: '课程编号', key: 'courseId', width: 15 },
      { header: '课程名称', key: 'courseName', width: 20 },
      { header: '学期', key: 'term', width: 15 },
      { header: '成绩', key: 'score', width: 10 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加示例数据
    worksheet.addRow({
      studentId: '2023001',
      studentName: '张三',
      courseId: 'CS101',
      courseName: '计算机基础',
      term: '2023-2024-1',
      score: 85
    });
    
    worksheet.addRow({
      studentId: '2023002',
      studentName: '李四',
      courseId: 'CS101',
      courseName: '计算机基础',
      term: '2023-2024-1',
      score: 92
    });
    
    // 添加说明
    worksheet.addRow({});
    worksheet.addRow({studentId: '说明：请按照以上格式填写成绩数据，然后上传'});
    
    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=grades_template.xlsx');
    
    // 将Excel文件写入响应
    await workbook.xlsx.write(res);
    res.end();
    
  } catch (error) {
    console.error('获取成绩导入模板错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 导出成绩
router.post('/export', auth, async (req, res) => {
  console.log('成绩导出路由被调用');
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, courseId, status, term } = req.body;
    
    console.log('导出成绩请求参数:', { classId, courseId, status, term });
    
    // 构建查询条件 - 使用与获取成绩列表相同的查询方式
    let conditions = [];
    let params = [];
    
    if (classId && classId !== '' && classId !== '0') {
      conditions.push('c.id = ?');
      params.push(classId);
    }
    
    if (courseId && courseId !== '' && courseId !== '0') {
      conditions.push('co.id = ?');
      params.push(courseId);
    }
    
    if (status && status !== '' && status !== '全部') {
      if (status === '通过') {
        conditions.push('g.score >= 60');
      } else if (status === '不通过') {
        conditions.push('g.score < 60');
      }
    }
    
    if (term && term !== '' && term !== '全部') {
      conditions.push('co.semester = ?');
      params.push(term);
    }
    
    const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
    
    // 使用与获取成绩列表相同的查询语句
    const query = `
      SELECT g.id, u.userId as studentNumber, u.name as studentName, 
       c.className, co.name as courseName, g.score, co.semester as term
       FROM grades g
       JOIN users u ON g.studentId = u.id
       JOIN classes c ON u.classId = c.id
       JOIN courses co ON g.courseId = co.id
       ${whereClause}
       ORDER BY c.className, u.userId, co.name`;
    
    console.log('执行SQL查询:', query);
    console.log('查询参数:', params);
    
    const [grades] = await pool.query(query, params);
    
    console.log(`查询到 ${grades.length} 条成绩记录`);
    
    if (grades.length === 0) {
      return res.status(404).json({ message: '未找到符合条件的成绩记录' });
    }
    
    // 创建Excel文件
    const Excel = require('exceljs');
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('成绩数据');
    
    // 添加表头 - 根据实际字段调整
    worksheet.columns = [
      { header: '学号', key: 'studentNumber', width: 15 },
      { header: '姓名', key: 'studentName', width: 12 },
      { header: '班级', key: 'className', width: 15 },
      { header: '课程', key: 'courseName', width: 20 },
      { header: '学期', key: 'term', width: 15 },
      { header: '成绩', key: 'score', width: 10 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加数据 - 根据实际字段调整
    grades.forEach(grade => {
      worksheet.addRow({
        studentNumber: grade.studentNumber,
        studentName: grade.studentName,
        className: grade.className,
        courseName: grade.courseName,
        term: grade.term,
        score: grade.score
      });
    });
    
    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=grades_export.xlsx');
    
    // 将Excel文件写入响应
    await workbook.xlsx.write(res);
    res.end();
    
    console.log('成绩导出完成');
    
  } catch (error) {
    console.error('导出成绩错误:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

module.exports = router;